import pandas as pd
from bs4 import BeautifulSoup
from pypinyin import pinyin, Style
import pymysql
from sqlalchemy import create_engine
import re
from typing import List, Dict, Union, Callable, Tuple

# 多音字映射表
POLYPHONE_MAP = {
    '曾': ['zeng', 'ceng'],
    '行': ['xing', 'hang'],
    '乐': ['le', 'yue']
}

def get_mysql_connection():
    try:
        connection = pymysql.connect(
            host='10.16.0.216',
            user='root',
            password='123test',
            database='kycg',
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )
        return connection
    except Exception as e:
        print(f"连接MySQL数据库时出错: {e}")
        return None

def read_from_mysql(query):
    connection = get_mysql_connection()
    if not connection:
        return None

    try:
        with connection.cursor() as cursor:
            cursor.execute(query)
            result = cursor.fetchall()
            return result
    except Exception as e:
        print(f"执行MySQL查询时出错: {e}")
        return None
    finally:
        connection.close()

def read_excel_rows(file_path, sheet_name=0):
    try:
        # 读取 Excel 文件
        df = pd.read_excel(file_path, sheet_name=sheet_name)

        rows = df.values.tolist()

        # rows = [df.columns.tolist()] + df.values.tolist()
        return rows

    except Exception as e:
        print(f"读取 Excel 文件时出错: {e}")
        return None


def split_string_by_semicolon(input_string):
    # 检查输入是否为字符串
    if not isinstance(input_string, str):
        raise TypeError("输入必须是字符串类型")

    # 去除字符串首尾空白字符
    stripped_string = input_string.strip()

    # 如果字符串为空，返回空数组
    if not stripped_string:
        return []

    # 以中文分号"；"为分隔符进行拆分
    split_result = stripped_string.split(";")

    # 去除每个结果项的首尾空白字符
    cleaned_result = [item.strip() for item in split_result if item.strip()]

    return cleaned_result

def load_names_from_mysql(table_name, name_column):
    query = f"SELECT {name_column} FROM {table_name}"
    results = read_from_mysql(query)
    if results:
        return [item[name_column] for item in results]
    return []

def load_names_from_mysql1(table_name, name_column,teacher_name):
    query = f"SELECT {name_column} FROM {table_name} where DSXM='{teacher_name}'"
    results = read_from_mysql(query)
    if results:
        return [item[name_column] for item in results]
    return []

def cnki_html_to_excel(input_path, output_path):
    # 解析HTML
    with open(input_path, 'r', encoding='utf-8') as f:
        soup = BeautifulSoup(f.read(), 'html.parser')

    table = soup.find('table')
    headers = [th.get_text(strip=True).split('-')[-1] for th in table.find('tr').find_all(['th', 'td'])]
    data = [[td.get_text(strip=True) for td in tr.find_all('td')] for tr in table.find_all('tr')[1:]]
    df = pd.DataFrame(data, columns=headers)

    # 清洗数据
    df = df[~df.iloc[:, 0].str.contains('SrcDatabase|来源库', na=False)]

    # 导出Excel
    df.to_excel(output_path, index=False, engine='openpyxl')
    print(f"文件已保存至：{output_path}")

def match_chinese_name_to_table(pinyin_list, table_name,name_column)->list[str]:
    chinese_names = load_names_from_mysql(table_name,name_column)

    return exact_match_chinese_names(pinyin_list, chinese_names)

def exact_match_chinese_names(names1: List[str], names2: List[str]) -> List[str]:
    # 使用集合的交集操作来找出完全相同的姓名
    set1 = set(names1)
    set2 = set(names2)
    return list(set1 & set2)

def remove_common_name(arr1, arr2):

    # 获取两个数组的值集合
    values1 = set(arr1)
    values2 = set(arr2)

    # 找出共有的值
    common_values = values1 & values2

    # 从两个列表中删除共有项
    new_list1 = [item for item in arr1 if item not in common_values]

    return new_list1

cnki_html_to_excel('C:\\Users\\Administrator\\Downloads\\CNKI-20250615145622929.xls', '标准输出.xlsx')

file_path = '标准输出.xlsx'

all_rows = read_excel_rows(file_path, 0)

if all_rows:
        # 打印每一行数据
    for i, row in enumerate(all_rows, 1):

        if len(row) > 1:

            pinyin_result = split_string_by_semicolon(row[2])
            teacher_result= match_chinese_name_to_table(pinyin_result, 't_jzg','XM')
            student_result = match_chinese_name_to_table(pinyin_result, 't_yjs','XM')
            repetition_result = exact_match_chinese_names(student_result, teacher_result)
            teacher_result = remove_common_name(teacher_result, repetition_result)
            student_result = remove_common_name(student_result, repetition_result)
            if len(repetition_result) != 0:
                teacher_result1 = match_chinese_name_to_table(repetition_result, 't_yjs_dyds','DSXM')
                student_identity = remove_common_name(repetition_result, teacher_result1)
                student_result = student_result + student_identity
                #print(student_result)
                #print(f"教师{teacher_identity}")
                if len(student_result) != 0:
                    if len(teacher_result1) != 0:
                        for item in teacher_result1:
                            dy_student = load_names_from_mysql1('t_yjs_dyds','XM',item)
                            #print(f"指导学生{dy_student}")
                            if len(exact_match_chinese_names(student_result, dy_student)) > 0:
                                teacher_result.append(item)
                            else:
                                for j in teacher_result:
                                    dy_student1 = load_names_from_mysql1('t_yjs_dyds', 'XM', j)
                                    if len(exact_match_chinese_names([item], dy_student1)) > 0:
                                        student_result.append(item)
                else:
                    pinyin_result = pinyin_result + teacher_result1
            pinyin_result = remove_common_name(pinyin_result,teacher_result)
            pinyin_result = remove_common_name(pinyin_result,student_result)
            print(f"教师{teacher_result}")
            print(f"学生{student_result}")
            print(f"重名{repetition_result}")
            print(f"未处理{pinyin_result}")
